-- @owner: opentestcase009
-- @date: 2023/1/4
-- @testpoint: connect by子句,结合for update/share/no key update/key share子句,合理报错


--step1:创建普通表，插入数据;expect:建表成功，插入数据成功
drop table if exists t_connectby_021 cascade;
create table t_connectby_021(name varchar, id int, fatherid int);
insert into t_connectby_021 values('A', 1, 0), ('B', 2, 1),('C',3,1),('D',4,1),('E',5,2),('F',0,1),('G',1,2);

--step2:结合for update子句，查询id=1作为初始集合的递归查询;expect:不支持，合理报错
start transaction;
select * from t_connectby_021 start with id=1 connect by nocycle prior id = fatherid for update;
rollback;

--step3:结合for share子句，查询id=1作为初始集合的递归查询;expect:不支持，合理报错
start transaction;
select * from t_connectby_021 start with id=1 connect by nocycle prior id = fatherid for share;
rollback;

--step4:结合for no key update子句，查询id=1作为初始集合的递归查询;expect:不支持，合理报错
start transaction;
select * from t_connectby_021 start with id=1 connect by nocycle prior id = fatherid for no key update;
rollback;

--step5:结合for key share子句，查询id=1作为初始集合的递归查询;expect:不支持，合理报错
start transaction;
select * from t_connectby_021 start with id=1 connect by nocycle prior id = fatherid for key share;
rollback;

--step6:清理环境;expect:成功
drop table t_connectby_021 cascade;
